const connections = require('../app/database');

class MomentService {

    // CREATE TABLE IF NOT EXISTS `moment` (
    //     id INT PRIMARY KEY AUTO_INCREMENT,
    //     content VARCHAR(1000) NOT NULL,
    //     user_id INT NOT NULL,
    //     createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    //     updateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    //     FOREIGN KEY(user_id) REFERENCES users(id)
    // );

    async create(userId, content) {
        console.log("接收参数", userId, content);

        const statement = `INSERT INTO moment (content, user_id) VALUES (?, ?);`;

        const [result] = await connections.execute(statement, [content, userId]);

        return result;
    };

    async getMomentById(id) {
        // const statement = `SELECT * FROM moment WHERE id = ?;`;
        const statement = `
            SELECT 
            m.id id, m.content content, m.updateAt updateTime, m.createAt createTime,
            JSON_OBJECT('id', u.id, 'name', u.name, 'avatarUrl', u.avatar_url) author
            FROM moment m
            LEFT JOIN users u ON m.user_id = u.id
            WHERE m.id = ?;
        `;
        const result = await connections.execute(statement, [id]);

        return result[0];
    }

    async getMomentList(offset, size) {
        const statement = `
            SELECT 
            m.id id, m.content content, m.updateAt updateTime, m.createAt createTime,
            JSON_OBJECT('id', u.id, 'name', u.name, 'avatarUrl', u.avatar_url) author,
                (SELECT COUNT (*) FROM comment c WHERE c.moment_id = m.id) commentCount,
                (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount,
                (SELECT JSON_ARRAYAGG(CONCAT('http:/localhost:8000/moment/images/', file.filename)) FROM file WHERE m.id = file.moment_id) images
            FROM moment m
            LEFT JOIN users u ON m.user_id = u.id
            LIMIT ?, ?;
        `;
        const [result] = await connections.execute(statement, [offset, size]);
        return result;
    }

    async update(content, momentId) {
        const statement = `
            UPDATE moment SET content = ? WHERE id = ?;
        `;
        const [result] = await connections.execute(statement, [content, momentId]);
        return result;
    }

    async remove(momentId) {
        const statement = `
            DELETE FROM moment WHERE id = ?;
        `;
        const [result] = await connections.execute(statement, [momentId]);
        return result;
    }

    async hasLabel(momentId, labelId) {
        const statement = `
            SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;
        `;
        const [result] = await connections.execute(statement, [momentId, labelId]);

        return result[0] ? true : false;
    }

    async addLabel(momentId, labelId) {
        const statement = `
            INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);
        `;
        const [result] = await connections.execute(statement, [momentId, labelId]);

        return result;
    }
}

module.exports = new MomentService();